It has already been decided to use a independent database system to store the data. In this section we have separated the database's analysis, 
design and implementation.

\subsubsection{Analysis}
In this section the website's functional requirements are going to be analyzed to establish the database's requirements.
\emph{Figure \ref{database_er}}

\begin{enumerate}
  \item The database must be able to store a user with the authentication information, nick and password. This user is going to have name, 
    surname, email, phone, country and address. Due to the need of administrators in the web site, there is going to be another field that 
    determines whether the user is an administrator or is not.
  \item Each mobile user is going to have his own positions, although the android application needs just the last position the web site needs 
    all of them to show the user's history in time.
  \item The user has to be able to make friend requests and see the request that he has. Then this can be rejected, so deleted, or accepted. If 
    a request is accepted it should be deleted from request and create a new friendship between the users. There has to be a direct relation 
    that specifies that two users are friends.
  \item Last thing to store is the notes that the users create. These notes always have a text and a position so as to be displayed in the 
    right location of the map. These notes can also have photos.
\end{enumerate}

  \begin{figure}[ht]
    \centering
    \includegraphics[width=1\textwidth ]{WebService/images/database_er.png} 
    \caption{Database Entity Relation Diagram}
    \label{database_er}
  \end{figure}

\subsubsection{Design}

In this section the database tables and fields are going to be chosen.
\emph{Figure \ref{database}}

\begin{enumerate}
  \item The is going to be a user entity with a primary key ID that identifies a single user with the attributes nick and password for 
    authentication, name, surname, email, phone, country, address and administrator to know whether it is or is not an administrator.
  \item Another entity is going to store all users position in time, this means the users IDs, the time and the position. The position in 
    GPS is composed of two float numbers, one for the latitude and the other for the longitude.
  \item Two relationship between friends are going to store friend requests and friendships. In the request the first field is going to tell 
    who make the request and the second field who receives it. In the friendship table due to efficiency is decided to set the lower ID in the 
    first field and the higher in the second. This is going to make faster checking if a two users are friends. For example, to check if 5 
    and 3 are friends just sort them and look for (3,5) row instead of looking for (3,5) or (5,3).
  \item The note entity has it's own ID to identify a single one, as a primary key. Other fields are, the text associated, the owner's ID, the 
    position, when was created and the photo, if attached. Due to the fact that the photo is optional and this field's size is not constant and 
    is pretty big(binary longblob) this field is stored in another table. This table is going to have the note's ID and the note's photo. Despite 
    that is still necessary to look in the photo table to check if the note has a photo is to assume that now the chance to have the note table 
    into the database cache is much bigger, improving the performance. \newline
  \emph{For future improvements is considered adding a field to the note table that determines whether the note has a photo attached.}
  \item The identification fields, unsigned 10 bit integers are used, for password field 40 byte string because the SHA hash function result is 
    that length and for the photo longblob based on photos that the android's camera produces.
\end{enumerate}


  \begin{figure}[ht]
    \centering
    \includegraphics[width=1\textwidth ]{WebService/images/database.png} 
    \caption{Database Table Design}
    \label{database}
  \end{figure}

The interface \emph{ffLocationDBIface} is specified in order to accomplish the requirement that asks for an easily extensible storage system.

\subsubsection{Choosing software}
We have decided to use \emph{MySQL} database due to the following reasons:
\begin{enumerate}
  \item \emph{MySQL} open source software is provided under the \emph{GPL License}, what means it is for free.
  \item The whole team has previous experience with this software.
  \item It has the innoDB engine which supports foreign keys constraints.
  \item It is the world's most popular open source database.
    \begin{enumerate}
      \item Is supposed to be reliable.
      \item A large amount of information can be found on the \emph{Internet}.
      \item Offers standard database driver connectivity with applications and tools that are compatible with industry standards 
	  \emph{ODBC} and \emph{JDBC}.
    \end{enumerate}
\end{enumerate}

\subsubsection{Implementation}
\paragraph{Decisions depending on software}
As the database design has relations between tables we found strongly beneficial to implement using foreign keys. Due to this fact we decided 
to use the \emph{innoDB} engine, as it is the only one that supports this feature natively. This foreign key constraint in the tables working 
under the \emph{innoDB} engine provides the following advantages:
\begin{enumerate}
  \item Assuming the proper design of the relationships, foreign key constraints made it more difficult for us to introduce an inconsistency 
    into the database. This means that the database is going to throw an exception each time there is an attempt to break this constraints.
  \item Is easier check these constraints in the database than in the program. Even more if someone decides to continue with this project and 
    write another program that accesses the database directly. In this case these constraints have to be checked again in the new program, 
    and if there are changes, changing the whole system required.
  \item Using cascading updates and deletions can simplify the application code a lot. Let say a users wants to be removed from the system. 
    If the constraints referencing this user are set in cascade mode, all the data in the database about this user is going to be deleted when 
    the user row is deleted.
\end{enumerate}

\paragraph{Setting up the database system}
At this time we decide to use \emph{phpmyadmin} to handle the administration of \emph{MySQL}. The main reason is that we already have installed 
an \emph{Apache Server} with \emph{PHP5} used for the website and we have previous experience with this software. Then we make the following 
steps:
\begin{enumerate}
\item Using this software we access the database as root and we create a new administration account called "\emph{fflocation}" with full access 
  to a new database called also "\emph{fflocation}". This user has permission to create, change or delete the table content and the table 
  structure, create users and grant rights to other users. once we finish with the root account, this means no risk for the database system. 
  From now on we are going to use the "\emph{fflocation}" account for developing and administration purpose.
\item First thing is creating the database using the \emph{phpmyadmin} interface. We create each table with all fields and set the primary keys, 
  index fields and we use the \emph{innoDB} engine. Ones this is done we create all the constraints using the foreign key references between 
  tables.
\item Now that the database is ready to be used we can add the Web service client. Using the "\emph{fflocation}" we create another user with a 
  random password that is going to be given to the Web service to connect. We grant this account data rights as select, insert, update and delete. 
  By now the Web service account is ready to be used.
\end{enumerate}

Note that this could be done using mysql client instead of using phpmyadmin, for example to create the "\emph{user}" table the following 
\emph{SQL} statement could be used:

\begin{lstlisting}[captionpos=b,label=ws-mysql_CreateTable, caption={\textit{DBIface MySQL constructor}},language=Sql, basicstyle=\small, 
frame=single]
CREATE TABLE EXISTS `user` (
  `ID` int(10) unsigned NOT NULL AUTO_INCREMENT
      COMMENT 'User ID',
  `Nick` varchar(32) NOT NULL
      COMMENT 'Login nickname',
  `Password` varchar(40) NOT NULL
      COMMENT 'SHA(Password)',
  `Name` varchar(32) NOT NULL
      COMMENT 'User real name',
  `Surname` varchar(64) DEFAULT NULL
      COMMENT 'User real surname',
  `Email` varchar(64) NOT NULL
      COMMENT 'User''s email',
  `Phone` int(10) unsigned DEFAULT NULL
      COMMENT 'User''s phone number',
  `Country` varchar(32) NOT NULL
      COMMENT 'User''s country',
  `Address` varchar(128) DEFAULT NULL
      COMMENT 'User''s address',
  `Administrator` tinyint(1) NOT NULL DEFAULT '0'
      COMMENT 'Is user administrator?',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `Nick` (`Nick`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
\end{lstlisting}

In this \emph{SQL} statement all the user's table fields are defined. For example the "\emph{ID}" is an integer field that can not be null, 
that auto increments and that is the primary key. Nick is a unique string and administrator is 0 or 1, a boolean type, that by default is 0, false.

\paragraph{Java}
As a result of this section a main interface is implemented for \emph{Java} that accesses the mysql database. First of all we get the 
\emph{MySQL Connection/J} which is a native Java driver to convert \emph{JDBC}  calls into the \emph{MySQL} databases protocol over the network. 
The following implementation of the database interface requires this package that can be found in \emph{http://dev.mysql.com/usingmysql/java/}.
This is the constructor of the class that implements the database interface. We can see in the \emph{URL} how it is set to connect to to 
local host's \emph{"fflocation"} database. Later in the driver manager we enter the username an password that grants access to the pointed 
database in local host. Even though, by default we did not use auto commit, we changed it to be true because the service needs too few 
transactions.

\begin{lstlisting}[captionpos=b,label=ws-mysql_construct, caption={\textit{DBIface MySQL constructor}},language=Java, basicstyle=\small, 
frame=single]
private ffLocationMySQL(){
  try
  {
    String url = "jdbc:mysql://localhost/fflocation";
    connection = DriverManager.getConnection(url,
                        "fflocation", "fflocation");
    connection.setAutoCommit(true);
  }catch (SQLException anException){
    while (anException != null){
    System.out.println("SQL Exception:  " + 
                        anException.getMessage());
    anException = anException.getNextException();
    }
  }catch (java.lang.Exception anException){
    anException.printStackTrace();
  }
}
\end{lstlisting}

In this code we can see how an insert is performed to register a new position for a user. First we prepare the insert statement and fill 
it with the owner's identification number and the positions fields, which are two float numbers for latitude and longitude.

\begin{lstlisting}[captionpos=b,label=ws-mysql_newPosition, caption={\textit{DBIface MySQL constructor}},language=Java, basicstyle=\small, 
frame=single]
@Override
public boolean newPosition(int userID, float lat, float lon) {
  PreparedStatement insertSentence = null;
  try{
    insertSentence = connection.prepareStatement(
    "INSERT INTO `fflocation`.`history` (`ID`,
         `When`, `Latitude`, `Longitude`) VALUES (" +
            "?, NOW(), ?, ?);");
    insertSentence.setInt(1,userID);
    insertSentence.setFloat(2,lat);
    insertSentence.setFloat(3,lon);
    insertSentence.executeUpdate();
  }catch(Exception e){
    e.printStackTrace();
    return false;
  }
return true;
}
\end{lstlisting}


This is the method that adds a relationship between two friends. We can see here just like in the areFriends function, which checks whether 
a relationship exists, that the user's identification numbers are sorted in order to improve the performance, like it was mentioned in the 
design section.

\begin{lstlisting}[captionpos=b,label=ws-mysql_addFriends, caption={\textit{DBIface MySQL constructor}},language=Java, basicstyle=\small, 
frame=single]
@Override
public boolean addFriends(int tid1, int tid2) {
  int id1,id2;
  if(tid1==tid2) return true;
  if(tid1<tid2){
    id1=tid1;
    id2=tid2;
  }else{
    id1=tid2;
    id2=tid1;
  }
  PreparedStatement insertSentence = null;
  try{
    insertSentence = connection.prepareStatement(
        "INSERT INTO
            `fflocation`.`friend` (`IDUser1`, `IDUser2`)" +
              "VALUES ( ?, ?);");
    insertSentence.setInt(1, id1);
    insertSentence.setInt(2, id2);
    insertSentence.executeUpdate();			
  }catch(Exception e){
    e.printStackTrace();
    return false;
  }
  return true;
  }
\end{lstlisting}


\subsubsection{API for java}
To sum up with the database we define the \emph{FFLocationIFace} interface that is implemented by the \emph{FFLocationMySQL} class:
The \emph{exists} function returns true if the nick that was given as a parameter is alredy been used, false otherwise.
In order to create a new user in the system, a new account, \emph{newUser} can be used, which takes as a parameter a \emph{User} class object 
with the user's information. It return whether it succeed. To load a user by identification number or by nick overloaded function 
\emph{loadUser} can be used. To change a user the API offers the \emph{saveUser} function that takes a \emph{User} object as a parameter and 
tries to change it, then it returns true if succeed or otherwise false. The \emph{delUser} takes as a parameter the id of the user that is 
going to be deleted and returns true if it was deleted, otherwise false.

To get all the users in the system by pages \emph{getUsers} is used, which takes as a parameter the count of users per page and the page number 
requested. In order to register a user's new position the \emph{newPosition} method is used, which takes the user's identification number and 
the position, that consists in two float numbers, as parameters. The \emph{getPositions} returns the last n positions of a user that matches 
the given id, where n is a given parameter. Null is returned if something goes wrong. The \emph{addFriends} takes as parameters two user 
identifications and creates a relationship between them. Then returns whether it succeed or not. The \emph{areFriends} takes as parameters 
two user identifications and returns whether they are or they are not friends.

To create a new relationship request from one user to another one the \emph{newRequest} method must be used. The first parameter is the 
identification of the user that makes the request and the second is who receives it. The \emph{getRequests} returns the friendship requests 
that a user has. The method takes the user's identification number as a parameter and returns null if something is not correct. The 
\emph{delRequest} takes as parameters two user identifications and tries to delete the friendship request from the first user to the second 
one. The function returns whether it succeed. The \emph{getFriends} returns a list of friends of the given user identification or \emph{null} 
if something fails. 

In order to create a new note the \emph{setNote} method can be used, which takes a \emph{Note} object and tries to store it in the database. 
It returns whether the note was stored. The \emph{getNotes} returns the last n notes of the user that matches the given id, where n is a given 
parameter. Null is returned if something goes wrong. The \emph{getNote} returns a Note object with the filled fields with the note information 
from the database with the identification number that was given. It returns \emph{null} if something goes wrong. To remove a note the 
\emph{API} offers the method \emph{removeNote} which takes as a parameter the note's identification number and returns whether it was deleted.
Finally, in order to look for a user the \emph{searchFriend} function can be used, which returns the list of users that match the fields that 
this methods takes as parameters such us nick, name, surname and country.